---
title: "Shared Database"
type: concept
created: 2026-04-18
updated: 2026-04-18
sources: ["raw/notes/memory.md"]
tags: [infrastructure, database, mysql, docker, shared-db]
---

# Shared Database

All Pickatale services share a single MySQL 8 Docker container (`shared-db`). This consolidation happened on 2026-04-05 — prior to that, each service had its own database container, consuming ~13 GB RAM collectively.

## Container Configuration

| Setting | Value |
|---|---|
| Container name | `shared-db` |
| Docker image | `mysql:8` |
| Host port | **3316** (not 3306 — that port was occupied at migration time) |
| Internal port | 3306 |
| `--restart` | `unless-stopped` |
| Root password | Stored in secure env on server — contact server admin |

## Connection Pattern

All services connect using this pattern:

```
mysql://<DB_USER>:<DB_PASSWORD>@<DB_HOST>:<DB_PORT>/<DB_NAME>
```

- `172.17.0.1` is the Docker host gateway — accessible from any container
- Port `3316` is the host-bound port
- Each service has its own database and user — **never share credentials across services**

**Example (from a service `.env`):**
```
DATABASE_URL=mysql://<DB_USER>:<DB_PASSWORD>@<DB_HOST>:<DB_PORT>/teacher_portal
```

## Migration Rules (Hard-Won Lessons)

From the 2026-04-05 consolidation:

1. **`docker compose restart` does NOT reload env** — always use `docker compose down && docker compose up -d` after any `.env` changes
2. **Check BOTH `.env` AND `docker-compose.yml`** — hardcoded values in `docker-compose.yml environment:` block will override `.env`
3. **Verify user passwords after migration** with actual login test — not just `SHOW USERS`
4. **Row count verification**: use `SELECT COUNT(*)`, NOT `information_schema.TABLE_ROWS` — InnoDB estimates are unreliable

## RAM Impact

Consolidation freed ~5-7 GB:
- Before: ~13 GB used (individual containers)
- After: ~8 GB used (single shared container + services)
- ~7 GB now available for future services

## All Project Databases

Every project database on the shared instance:

| Database | Service | Status |
|---|---|---|
| `account_center` | Account Center | ✅ Live |
| `teacher_portal` | Teacher Portal | ✅ Live |
| `reader_app` | Reader App | ✅ Live |
| `telemetry` | Telemetry Service | ✅ Live |
| `learner_bot` | Learner Bot | ✅ Live |
| `curriculum` | Curriculum Mapper | ✅ Live |
| `adaptive_content` | Adaptive Engine | ✅ Live |
| `learner` | Learner Profile Service | ✅ Live |
| `lrs` | LRS | ✅ Live |
| `content_meta` | Content Service | ✅ Live |
| `onboard` | Interest Onboarding / COPPA | ✅ Built |
| `analytics` | Analytics + ETL | ✅ Live |
| `fluency` | Fluency Assessment | ✅ Live |
| `recommendation` | Recommendation Engine | ✅ Live |

## Adding a New Database

When adding a new service database:

```bash
# Connect as root
mysql -h 172.17.0.1 -P 3316 -u root -p

# Create database and user
CREATE DATABASE new_service;
CREATE USER 'new_service_user'@'%' IDENTIFIED BY 'STRONG_PASSWORD';
GRANT ALL PRIVILEGES ON new_service.* TO 'new_service_user'@'%';
FLUSH PRIVILEGES;
```

Each new service gets:
- Its own database (named after the service)
- Its own user (named `<service>_user`)
- Credentials stored in that service's `.env` only

## Backup Strategy

⚠️ **No automated backups configured as of 2026-04-18.** This is a risk for a production system. Recommended:

1. Daily `mysqldump` of all databases to S3 or off-server storage
2. Point-in-time recovery via MySQL binary logging
3. Retention: 30 days minimum

⏳ **DECISION-XX:** Automated backup strategy not yet approved or implemented. Must be resolved before first real school pilot.

## Related Pages

- [[concepts/infrastructure/index|Infrastructure]] — server specs, Docker, Caddy overview
- [[concepts/data-model/index|Data Model]] — service → database ownership map